/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.surveilans.model;

import com.surveilans.classes.DataSurveilans;
import com.surveilans.classes.Kabupaten;
import com.surveilans.classes.Penyakit;
import com.surveilans.classes.Provinsi;
import com.surveilans.classes.Unit;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Yudi Retanto
 */
public class UnitModel extends Model {

    public UnitModel() {
        super();
    }
    
    public ArrayList<Provinsi> getProvinsi(){
        ArrayList<Provinsi> ap = new ArrayList<Provinsi>();
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM provinsi";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Provinsi p = new Provinsi(rs.getInt("id"), rs.getString("nama"));
                ap.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ap;
    }
    
    public ArrayList<Kabupaten> getKabupaten(){
        ArrayList<Kabupaten> ap = new ArrayList<Kabupaten>();
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM kabupaten";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Kabupaten p = new Kabupaten(rs.getInt("id"), rs.getString("nama"),rs.getInt("id_provinsi"));
                ap.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ap;
    }
    
    public ArrayList<Kabupaten> getKabupaten(int id_provinsi){
        ArrayList<Kabupaten> ap = new ArrayList<Kabupaten>();
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM kabupaten WHERE id_provinsi="+id_provinsi;
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Kabupaten p = new Kabupaten(rs.getInt("id"), rs.getString("nama"),rs.getInt("id_provinsi"));
                ap.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ap;
    }
    
    public ArrayList<Unit> getUnit(){
        ArrayList<Unit> ap = new ArrayList<Unit>();
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM unit LEFT JOIN provinsi ON unit.provinsi=provinsi.id LEFT JOIN kabupaten ON unit.kabupaten=kabupaten.id";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Unit p = new Unit();
                p.setId(rs.getInt("unit.id"));
                p.setProvinsi(rs.getInt("provinsi.id"));
                p.setKabupaten(rs.getInt("kabupaten.id"));
                p.setNama(rs.getString("unit.nama"));
                p.setUsername(rs.getString("username"));
                p.setAlamat(rs.getString("alamat"));
                p.setProvinsi_name(rs.getString("provinsi.nama"));
                p.setKabupaten_name(rs.getString("kabupaten.nama"));
                ap.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ap;
    } 

    public Unit getUnit(int id) {
        Unit ap = new Unit();
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM unit LEFT JOIN provinsi ON unit.provinsi=provinsi.id LEFT JOIN kabupaten ON unit.kabupaten=kabupaten.id WHERE unit.id="+id;
            ResultSet rs = stmt.executeQuery(query);

            rs.next();
            ap.setId(rs.getInt("unit.id"));
            ap.setProvinsi(rs.getInt("provinsi.id"));
            ap.setKabupaten(rs.getInt("kabupaten.id"));
            ap.setNama(rs.getString("unit.nama"));
            ap.setUsername(rs.getString("username"));
            ap.setAlamat(rs.getString("alamat"));
            ap.setProvinsi_name(rs.getString("provinsi.nama"));
            ap.setKabupaten_name(rs.getString("kabupaten.nama"));
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ap;
    }
    
    public void addUnit(String nama, String alamat,int prov, int kab, String username, String password){
        PreparedStatement stmt = null;
        try {
            String query = "INSERT INTO unit (nama,alamat,provinsi,kabupaten,username,password,tanggal_input) VALUES ('"+nama+"','"+alamat+"',"+prov+","+kab+",'"+username+"',MD5('"+password+"'),CURDATE())";
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateUnit(Unit u) {
        PreparedStatement stmt = null;
        try {
            String query = "UPDATE unit "
                    + "SET nama='"+u.getNama()+"',"
                    + "alamat='"+u.getAlamat()+"',"
                    + "username='"+u.getUsername()+"',"
                    + "provinsi="+u.getProvinsi()+","
                    + "kabupaten="+u.getKabupaten()+" "
                    + "WHERE id="+u.getId();
            stmt = con.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void updateUnitPwd(int unit_id,String pwd) {
        PreparedStatement stmt = null;
        try {
            String query = "UPDATE unit "
                    + "SET password=MD5('"+pwd+"') "
                    + "WHERE id="+unit_id;
            stmt = con.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void deleteUnit(int unit_id) {
        PreparedStatement stmt = null;
        try {
            String query = "DELETE FROM unit "
                    + "WHERE id="+unit_id;
            stmt = con.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int validate(String username, String password) {
        int userId = 0;
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM unit WHERE username='"+username +"' AND password=MD5('"+password+"')");

            rs.next();
            userId = rs.getInt("id");
        } catch (SQLException ex) {
            Logger.getLogger(UserModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return userId;
    }
    
    public String getUnitProvinsi(int id_unit){
        String ap = "";
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM unit LEFT JOIN provinsi ON unit.provinsi=provinsi.id WHERE unit.id="+id_unit;
            ResultSet rs = stmt.executeQuery(query);

            rs.next();
            ap = rs.getString("provinsi.nama");
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ap;
    }
    
    public String getUnitKabupaten(int id_unit){
        String ap = "";
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM unit LEFT JOIN kabupaten ON unit.kabupaten=kabupaten.id WHERE unit.id="+id_unit;
            ResultSet rs = stmt.executeQuery(query);

            rs.next();
            ap = rs.getString("kabupaten.nama");
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ap;
    }
    
    public String getUnitNama(int id_unit){
        String ap = "";
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM unit WHERE unit.id="+id_unit;
            ResultSet rs = stmt.executeQuery(query);

            rs.next();
            ap = rs.getString("nama");
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ap;
    }
}
